﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;


namespace JTB.DAL
{
    public class JTBHZ
    {
        static string connectionStr = ConfigurationManager.ConnectionStrings["JTBHZ"].ConnectionString;
        // static string connectionStr = "Data Source=112.74.68.23;Initial Catalog=JTBHZ;persist security info=True;user id=5Bus;password=yuexing;MultipleActiveResultSets=True";

        public List<string> GetTableNameList()
        {
            IDbConnection connection = new SqlConnection(connectionStr);
            var sql = @"select table_name from information_schema.tables";
            var result = connection.Query<string>(sql);
            connection.Close();

            return result.ToList();
        }

        public List<DataTable> GetToSyncTableList(List<string> tableNameList)
        {
            IDbConnection connection = new SqlConnection(connectionStr);
            var result = new List<DataTable>();
            foreach (var tableName in tableNameList)
            {
                DataTable table = new DataTable(tableName);
                var sql = string.Format("select * from {0} (nolock) where [SyncTime] is null", tableName);
                var dataReader = connection.ExecuteReader(sql);
                table.Load(dataReader);
                result.Add(table);
            }
            connection.Close();
            return result;
        }

        public int UpdateSyncStatus(List<DataTable> tableList)
        {
            IDbConnection connection = new SqlConnection(connectionStr);
            int count = 0;
            var syncTime = DateTime.Now;
            StringBuilder sbSql = new StringBuilder();
            foreach (var table in tableList)
            {
                var tableName = table.TableName;
                var idList = new List<string>();
                var rows = table.Select();
                if (rows.Length > 0)
                {
                    foreach (var row in rows)
                    {
                        idList.Add(row["ID"].ToString());
                    }
                    sbSql.AppendFormat("update {0} set SyncTime={1} where id in ({2}); ", tableName, syncTime, string.Join(",", idList));
                }
            }
            var sql = sbSql.ToString();
            if (!string.IsNullOrWhiteSpace(sql))
            {
                count += connection.Execute(sql);
            }
            connection.Close();
            return count;
        }
    }
}
